package com.x.erp.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.x.erp.model.User;
import com.x.framework.dao.BaseDao;

@Repository
public class UserDao extends BaseDao {

	private final static String selectUserListSql = "SELECT T1.*, T2.MARKET_NAME, T2.MARKET_LOGO, T3.SHOP_NAME FROM ERP_USER T1 JOIN ERP_MARKET T2 ON T1.MARKET_ID = T2.MARKET_ID LEFT JOIN ERP_SHOP T3 ON T1.SHOP_ID = T3.SHOP_ID WHERE true ";

	public List<User> selectUserList(User user) throws Exception {
		StringBuilder sb = new StringBuilder(selectUserListSql);
		List<Object> params = new ArrayList<>();
		selectSearch(sb, params, user);
		return this.queryForList(sb.toString(), User.class, params, user);
	}

	/**
	 *
	 * @param user
	 * @return 查询除车商车商员工外的人员
	 * @throws Exception
	 */
	public List<User> selectMarketUserList(User user) throws Exception {
		StringBuilder sb = new StringBuilder(selectUserListSql);
		List<Object> params = new ArrayList<>();
		selectSearch(sb, params, user);
		sb.append("AND T1.SHOP_ID IS NULL ");
		return this.queryForList(sb.toString(), User.class, params, user);
	}

	private final static String selectUserInfoById = "SELECT T1.*, T2.MARKET_NAME, T2.MARKET_LOGO, T3.SHOP_NAME, T3.SHOP_ADDRESS, T3.SHOP_AUCTION_STATE, T4.CITY_NAME FROM ERP_USER T1 JOIN ERP_MARKET T2 ON T1.MARKET_ID = T2.MARKET_ID LEFT JOIN ERP_SHOP T3 ON T1.SHOP_ID = T3.SHOP_ID JOIN BASE_CITY T4 ON T2.CITY_ID = T4.CITY_ID WHERE true ";

	public User selectUserInfoById(User user) throws Exception {
		StringBuilder sb = new StringBuilder(selectUserInfoById);
		List<Object> params = new ArrayList<>();
		selectSearch(sb, params, user);
		List<User> users = this.queryForList(sb.toString(), User.class, params);
		if(users != null && !users.isEmpty())
			return users.get(0);
		return null;
	}

	private final static String selectUserNumSql = "SELECT count(T1.user_id)  FROM ERP_USER T1  WHERE T1.USER_ID >= 0 ";

	public int selectUserNum(User user) {
		StringBuilder sql = new StringBuilder(selectUserNumSql);
		List<Object> params = new ArrayList<>();
		selectSearch(sql, params, user);
		return this.queryForInt(sql.toString(), params);
	}


	public int selectMarketUserNum(User user) {
		StringBuilder sql = new StringBuilder(selectUserNumSql);
		List<Object> params = new ArrayList<>();
		selectSearch(sql, params, user);
		sql.append("AND T1.SHOP_ID IS NULL ");
		return this.queryForInt(sql.toString(), params);
	}

	/**
	 * 通过marketId查询出最大的用户编号
	 *
	 * @param sql
	 * @param params
	 * @param user
	 */
	private final static String selectMaxUserNo = "SELECT MAX(t1.USER_NO) userNo " +
			"FROM erp_user t1 " +
			"WHERE t1.MARKET_ID=? ";

	public Map<String, String> selectMaxUserNo(User user) throws Exception {
		StringBuilder sql = new StringBuilder(selectMaxUserNo);
		List<Object> params = new ArrayList<>();
		params.add(user.getMarketId());
		RowMapper<Map<String, String>> mapper = new RowMapper<Map<String, String>>() {
			@Override
			public Map<String, String> mapRow(ResultSet rs, int rowNum) throws SQLException {
				Map<String, String> map = new HashMap<>();
				map.put("userNo", rs.getString("userNo"));
				if (StringUtils.isEmpty(map.get("userNo"))) {
					map.put("userNo", "0000");
				}
				return map;
			}
		};
		return this.queryForList(sql.toString(), mapper, params).get(0);
	}

	private void selectSearch(StringBuilder sql, List<Object> params, User user) {
		if (this.isString(user.getUserId())) {
			sql.append("AND T1.USER_ID = ? ");
			params.add(user.getUserId());
		}
		if (this.isNotNull(user.getUserType())) {
			sql.append("AND T1.USER_TYPE = ? ");
			params.add(user.getUserType());
		}
		if (this.isString(user.getUserName())) {
			sql.append("AND T1.USER_NAME = ? ");
			params.add(user.getUserName());
		}
		if (this.isString(user.getUserMobile())) {
			sql.append("AND T1.USER_MOBILE = ? ");
			params.add(user.getUserMobile());
		}
		if (user.getUserState() != null) {
			sql.append("AND T1.USER_STATE = ? ");
			params.add(user.getUserState());
		}
		if (this.isString(user.getMarketId())) {
			sql.append("AND T1.MARKET_ID = ? ");
			params.add(user.getMarketId());
		}
		if (this.isString(user.getCompanyId())) {
			sql.append("AND T1.COMPANY_ID = ? ");
			params.add(user.getCompanyId());
		}
		if (this.isString(user.getShopId())) {
			sql.append("AND T1.SHOP_ID = ? ");
			params.add(user.getShopId());
		}
		if (this.isString(user.getAccountId())) {
			sql.append("AND T1.ACCOUNT_ID = ? ");
			params.add(user.getAccountId());
		}
		if (this.isString(user.getWxOpenId())) {
			sql.append("AND T1.WX_OPEN_ID = ? ");
			params.add(user.getWxOpenId());
		}
	}

}
